﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Threading;
using System.IO;
using System.IO.Ports;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

namespace TCA_User
{
    public partial class Bill_List
    {
        #region
        public Int32 Total_Emp;
        public Excel.Application OpenXL;
        public Excel.Workbook OpenWB;
        public Excel.Worksheet OpenSheet;
        public Excel.Range OpenRng;
        #endregion

        enum ADD_BILL_RESULT
        {
            ADD_FAIL = 0,
            ADD_NEW = 1,
            UPDATE = 2
        };

        /**************************************************
         * Create & Open Excel File
         * ************************************************/
        private bool create_excel_file(string file_path)
        {
            bool result = false;
            if (File.Exists(file_path) == false)
            {
                //khoi tao cac doi tuong Com Excel de lam viec
                Excel.ApplicationClass xlApp;
                Excel.Worksheet xlSheet;
                Excel.Workbook xlBook;
                //doi tuong Trống để thêm  vào xlApp sau đó lưu lại sau
                object missValue = System.Reflection.Missing.Value;
                // khoi tao doi tuong Com Excel moi
                xlApp = new Excel.ApplicationClass();
                xlBook = xlApp.Workbooks.Add(missValue);
                //su dung Sheet dau tien de thao tac
                xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
                //không cho hiện ứng dụng Excel lên để tránh gây đơ máy
                xlApp.Visible = false;

                //save file
                xlBook.SaveAs(file_path, Excel.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, Excel.XlSaveAsAccessMode.xlExclusive, missValue, missValue, missValue, missValue, missValue);
                xlBook.Close(true, missValue, missValue);
                xlApp.Quit();

                // release cac doi tuong COM
                releaseObject(xlSheet);
                releaseObject(xlBook);
                releaseObject(xlApp);
                result = true;
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="file_path"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public Excel.Workbook Open_excel_file(string file_path, string password)
        {
            bool next, file_opened;
            int cont;
            Excel.Workbook oWB;
            try
            {
                //Start Excel and get Application object.
                OpenXL = new Excel.Application();
                OpenXL.Visible = false;

                // Open New WordBook
                next = false;
                cont = 0;
                file_opened = false;
                do
                {
                    oWB = (Excel.Workbook)OpenXL.Workbooks.Open(file_path, 2, false, 5, password, password,
                                            false, Excel.XlPlatform.xlWindows, "", true, true, 0, true, false, false);
                    file_opened = true;
                    if (oWB.ReadOnly == true)
                    {
                        oWB.Close(false, false, false);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
                        OpenXL.Quit();
                        cont++;
                        file_opened = false;
                        if (cont == 20)
                        {
                            next = true;
                        }
                        else
                        {
                            next = false;
                        }
                        Random random = new Random();
                        int randomNumber = random.Next(0, 10);
                        Thread.Sleep(randomNumber * 100);
                    }
                    else
                    {
                        next = true;
                    }
                } while (next == false);

                if (file_opened != true)
                {
                    MessageBox.Show("Can not open excel File.\n Please try again later!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    OpenXL.Quit();
                    return null;
                }
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
                MessageBox.Show(errorMessage, "Error");
                oWB = null;
            }
            return oWB;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oWB"></param>
        private void Close_WorkBook(Excel.Workbook oWB)
        {
            try
            {
                //Manipulate a variable number of columns for Quarterly Sales Data.
                if (oWB != null)
                {
                    oWB.Save();
                    oWB.Close(false, false, false);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
                }

                OpenXL.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(OpenXL);
            }
            catch (Exception ex)
            {
                // Bắt lỗi
                MessageBox.Show(ex.Message, "Error");
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        static public void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                throw new Exception("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
        //End Create & Open Excel File


        /************************************************************
         * Read File
         * **********************************************************/
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// <param name="first_col"></param>
        /// <param name="num_col"></param>
        /// <returns></returns>
        private string Get_Excel_Line(Excel.Worksheet sheet, int row, int first_col, int num_col)
        {
            Excel.Range cur_cell;
            string ret_str = "", cel_str;
            int col;

            for (col = first_col; col < num_col + first_col; col++)
            {
                if (col != first_col) ret_str += ";";
                cur_cell = (Excel.Range)sheet.Cells[row, col];
                cel_str = cur_cell.Text.ToString().Trim();
                ret_str += cel_str;
                if ((col == first_col) && (cel_str == "")) return "";
            }
            return ret_str.Trim();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="file_name"></param>
        /// <param name="fInfo"></param>
        /// <param name="table"></param>
        /// <param name="filter_str"></param>
        /// <param name="priKey_index"></param>
        /// <returns></returns>
        private bool ReadDataFromFile(string file_name, string fInfo, int first_row, DataTable table, string filter_str, int priKey_index)
        {
            String strLine = String.Empty;
            StreamReader myfile;
            int num_col;
            int row;


            ProgressBar1.Visible = true;
            if ((fInfo == ".xls") || (fInfo == ".XLS") || (fInfo == ".xlsx") || (fInfo == ".XLSX"))
            {
                OpenWB = Open_excel_file(file_name, "");
                row = first_row;
                num_col = table.Columns.Count;
                while ((strLine = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, 1, num_col)) != "")
                {
                    try
                    {
                        AddDataRowToTable(strLine, table, filter_str, priKey_index, ';');
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error At Line: " + row + ". With error message\n" + ex.ToString(), "Error");
                    }
                    ProgressBar1.Value = row % 100;
                    row++;
                }
                Close_WorkBook(OpenWB);
            }
            else if ((fInfo == ".csv") || (fInfo == ".CSV"))
            {
                myfile = File.OpenText(file_name);
                if ((strLine = myfile.ReadLine()) != null)
                {
                    row = 0;
                    while (strLine != null)
                    {
                        try
                        {

                            AddDataRowToTable(strLine, table, filter_str, priKey_index, ',');
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Error At Line: " + row + ". With error message\n" + ex.ToString(), "Error");
                        }
                        ProgressBar1.Value = row % 100;
                        row++;
                        strLine = myfile.ReadLine();
                    }
                }
                myfile.Close();
            }

            ProgressBar1.Visible = false;
            return true;
        }

        // End Read File
        
        /***************************************************
         * Import data from excel to Datatable
         * *************************************************/

        private bool AddDataRowToTable(String strCSVLine, DataTable dt, string filter_str, int priKey_index, char split_char)
        {
            int idx = 0;
            string priKey_var;
            string filterExpression = "";
            String[] strVals = strCSVLine.Split(split_char);
            Int32 iTotalNumberOfValues = strVals.Length;
            DataRow drow = dt.NewRow();
            idx = 0;

            if (strVals.Length > 0)
            {
                // Get Empl_ID
                if (strVals[priKey_index].Trim() != "")
                {
                    priKey_var = strVals[priKey_index].Trim();
                    // Check data in Table
                    filterExpression = filter_str + "'" + priKey_var + "'";
                    DataRow[] rows = dt.Select(filterExpression);

                    if (rows.Length == 1)
                    {
                        // @NOTE (Kien #1#): update current row
                        foreach (String strVal in strVals)
                        {
                            if (idx != 0)
                            {
                                if (strVal != "")
                                    rows[0][idx] = strVal.Trim();
                            }
                            idx++;
                        }
                    }
                    else if (rows.Length == 0)
                    {
                        // @NOTE (Kien #1#): insert new row
                        foreach (String strVal in strVals)
                        {
                            if (idx == priKey_index)
                            {
                                drow[idx] = priKey_var.Trim();
                            }
                            else
                            {
                                if (strVal != "")
                                {
                                    drow[idx] = DateTime.ParseExact((strVal.Trim()), "dd/MM/yy", System.Globalization.CultureInfo.CurrentCulture);
                                }
                            }
                            idx++;
                        }
                        dt.Rows.Add(drow);
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            return true;
        }
        private ADD_BILL_RESULT AddDataRowToBillTable(String strCSVLine, DataTable dt, string filter_str, int priKey_index, char split_char, string taxi_provider_name)
        {
            int idx = 0;
            string priKey_var;
            string filterExpression = "";
            String[] strVals = strCSVLine.Split(split_char);
            Int32 iTotalNumberOfValues = strVals.Length;
            DataRow drow = dt.NewRow();
            idx = 0;

            if (strVals.Length > 0)
            {
                // Get Bill_ID
                if (strVals[priKey_index].Trim() != "")
                {
                    priKey_var = strVals[priKey_index].Trim();
                    // Check in database
                    if (Check_Bill_Exits_Database(priKey_var, taxi_provider_name) != BILL_EXIST.NEW_BILL)
                    {
                        MessageBox.Show("Bill" + priKey_var + "was had already in database", "Dupplicate Bill ID");
                        return ADD_BILL_RESULT.ADD_FAIL;
                    }

                    // Check data in Table
                    filterExpression = filter_str + "'" + priKey_var + "'";
                    DataRow[] rows = dt.Select(filterExpression);

                    if (rows.Length == 1)
                    {
                        DialogResult result = MessageBox.Show("Bill: " + priKey_var + " Have Already in System. \nDo you want to upadte with new one?", "Error", MessageBoxButtons.YesNo);
                        if (result == DialogResult.Yes)
                        {
                            // @NOTE (Kien #1#): update current row
                            foreach (String strVal in strVals)
                            {
                                if (idx != 0)
                                {
                                    if (strVal != "")
                                    {
                                        if (idx == 2)
                                        {
                                            if (DateTpye == TCA_User.Common.DateStringType.DDMMYY)
                                            {
                                                rows[0][idx] = DateTime.ParseExact((strVal.Trim()), "dd/MM/yy", System.Globalization.CultureInfo.CurrentCulture);
                                            }
                                            else if (DateTpye == TCA_User.Common.DateStringType.MMDDYY)
                                            {
                                                rows[0][idx] = DateTime.ParseExact((strVal.Trim()), "MM/dd/yy", System.Globalization.CultureInfo.CurrentCulture);
                                            }
                                            else if (DateTpye == TCA_User.Common.DateStringType.YYMMDD)
                                            {
                                                rows[0][idx] = DateTime.ParseExact((strVal.Trim()), "yy/MM/dd", System.Globalization.CultureInfo.CurrentCulture);
                                            }
                                        }
                                        else
                                        {
                                            rows[0][idx] = strVal.Trim();
                                        }
                                    }
                                }
                                idx++;
                            }   // end for
                        } // End: (result == DialogResult.Yes)
                        return ADD_BILL_RESULT.UPDATE;
                    }
                    else if (rows.Length == 0)
                    {
                        // @NOTE (Kien #1#): insert new row
                        foreach (String strVal in strVals)
                        {
                            if (idx == priKey_index)
                            {
                                drow[idx] = priKey_var.Trim();
                            }
                            else
                            {
                                if (strVal != "")
                                {
                                    if (idx == 2)
                                    {
                                        if (DateTpye == TCA_User.Common.DateStringType.DDMMYY)
                                        {
                                            drow[idx] = DateTime.ParseExact((strVal.Trim()), "dd/MM/yy", System.Globalization.CultureInfo.CurrentCulture);
                                        }
                                        else if (DateTpye == TCA_User.Common.DateStringType.MMDDYY)
                                        {
                                            drow[idx] = DateTime.ParseExact((strVal.Trim()), "MM/dd/yy", System.Globalization.CultureInfo.CurrentCulture);
                                        }
                                        else if (DateTpye == TCA_User.Common.DateStringType.YYMMDD)
                                        {
                                            drow[idx] = DateTime.ParseExact((strVal.Trim()), "yy/MM/dd", System.Globalization.CultureInfo.CurrentCulture);
                                        }
                                    }
                                    else
                                    {
                                        drow[idx] = strVal.Trim();
                                    }
                                }
                            }
                            idx++;
                        }
                        dt.Rows.Add(drow);
                    }
                    else
                    {
                        return  ADD_BILL_RESULT.ADD_FAIL;
                    }
                }
            }
            return ADD_BILL_RESULT.ADD_NEW;
        }
        // End Import data from excel to Datatable

        /******************************************************
         * Export DataTable to Excel File
         * ****************************************************/
        /// <summary>
        /// 
        /// </summary>
        /// <param name="file_path"></param>
        /// <param name="tieude"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        private bool ExportDataToExcel(string file_path, string tieude, DataTable dt)
        {
            bool result = false;
            //khoi tao cac doi tuong Com Excel de lam viec
            Excel.ApplicationClass xlApp;
            Excel.Worksheet xlSheet;
            Excel.Workbook xlBook;
            //doi tuong Trống để thêm  vào xlApp sau đó lưu lại sau
            object missValue = System.Reflection.Missing.Value;
            //khoi tao doi tuong Com Excel moi
            xlApp = new Excel.ApplicationClass();
            xlBook = xlApp.Workbooks.Add(missValue);
            //su dung Sheet dau tien de thao tac
            xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
            //không cho hiện ứng dụng Excel lên để tránh gây đơ máy
            xlApp.Visible = false;
            int socot = dt.Columns.Count;
            int sohang = dt.Rows.Count;
            int i, j;

            if (file_path != "")
            {
                //set thuoc tinh cho tieu de
                xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1").Merge(false);
                Excel.Range caption = xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1");
                caption.Select();
                caption.FormulaR1C1 = tieude;
                //căn lề cho tiêu đề
                caption.HorizontalAlignment = Excel.Constants.xlCenter;
                caption.Font.Bold = true;
                caption.VerticalAlignment = Excel.Constants.xlCenter;
                caption.Font.Size = 15;
                //màu nền cho tiêu đề
                caption.Interior.ColorIndex = 20;
                caption.RowHeight = 30;
                //set thuoc tinh cho cac header
                Excel.Range header = xlSheet.get_Range("A2", Convert.ToChar(socot + 65) + "2");
                header.Select();

                header.HorizontalAlignment = Excel.Constants.xlCenter;
                header.Font.Bold = true;
                header.Font.Size = 10;
                //điền tiêu đề cho các cột trong file excel
                for (i = 0; i < socot; i++)
                {
                    xlSheet.Cells[2, i + 2] = dt.Columns[i].ColumnName;
                }
                //dien cot stt
                xlSheet.Cells[2, 1] = "STT";
                for (i = 0; i < sohang; i++)
                {
                    xlSheet.Cells[i + 3, 1] = i + 1;
                }

                //dien du lieu vao sheet
                ProgressBar1.Visible = true;
                for (i = 0; i < sohang; i++)
                {
                    for (j = 0; j < socot; j++)
                    {
                        ((Excel.Range)xlSheet.Cells[i + 3, j + 2]).NumberFormat = "@";
                        xlSheet.Cells[i + 3, j + 2] = dt.Rows[i][j].ToString() == "" ? dt.Rows[i][j] : dt.Rows[i][j].ToString().Trim();
                    }
                    // Update progress Bar
                    ProgressBar1.Value = i % 100;
                }
                ProgressBar1.Visible = false;

                //autofit độ rộng cho các cột
                for (i = 0; i < sohang; i++)
                {
                    ((Excel.Range)xlSheet.Cells[1, i + 1]).EntireColumn.AutoFit();
                }

                //save file
                xlBook.SaveAs(file_path, Excel.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, Excel.XlSaveAsAccessMode.xlExclusive, missValue, missValue, missValue, missValue, missValue);
                xlBook.Close(true, missValue, missValue);
                xlApp.Quit();

                // release cac doi tuong COM
                releaseObject(xlSheet);
                releaseObject(xlBook);
                releaseObject(xlApp);
                result = true;
            }
            return result;
        }

        public bool Export_Data2Excel(Button bt, DataTable dt, string title)
        {
            SaveFileDialog save_diaglog = new SaveFileDialog();
            string file_name, fInfo;
            string temp;

            save_diaglog.Filter = "Excel File (*.xls)|*.xls|All files (*.*)|*.*";
            if (save_diaglog.ShowDialog() == DialogResult.OK)
            {
                file_name = save_diaglog.FileName;
                fInfo = Path.GetExtension(save_diaglog.FileName);
                temp = bt.Text;
                bt.Text = "Exporting ...";
                bt.Enabled = false;
                if ((fInfo == ".xls") || (fInfo == ".xlsx"))
                {
                    ExportDataToExcel(file_name, title, dt);
                }
                bt.Enabled = true;
                bt.Text = temp;
            }
            MessageBox.Show("Export File thành công", "Thông báo");
            return true;
        }
        //End Export DataTable to Excel File

        /*******************************************************
         * Create Full Report
         * *****************************************************/
        public int Current_Excel_Row;

        public bool Create_full_report(string file_path)
        {
            int sheet_index = 0;
            //1. Check exist & Create Excel File
            if (File.Exists(file_path) == true)
            {
                DialogResult result1 = MessageBox.Show( "File '" + file_path + "' is existing.\n Do you want to overwrite it?",
		                                                "Warning",
		                                                MessageBoxButtons.YesNo);
                if (result1 != DialogResult.Yes)
                {
                    return false;
                }
            }else{
                create_excel_file(file_path);
            }
            //Create Excel File
            OpenWB = Open_excel_file(file_path, "");
            foreach (DataRow row in Department_TBL_Form.Data_dtb.Rows)
            {
                OpenWB.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }

            //2. Create Report for Department
            sheet_index = 4;
            ProgressBar1.Visible = true;
            foreach (DataRow row in Department_TBL_Form.Data_dtb.Rows)
            {
                Create_Department_Report(row["DEPARTMEMT_NAME"].ToString(), OpenWB, sheet_index);
                sheet_index++;
                ProgressBar1.Value = (sheet_index*10) % 100;
            }

            //3. Create Sumary Report
            Excel.Worksheet xlSheet;
            xlSheet = (Excel.Worksheet)OpenWB.Worksheets.get_Item(1);
            Create_Sumary_Report_All(xlSheet);

            //4. Create for each personal
            Create_EachPerson_Report_All((Excel.Worksheet)OpenWB.Worksheets.get_Item(2),
                                           (Excel.Worksheet)OpenWB.Worksheets.get_Item(3));
            
            ProgressBar1.Visible = false;

            Close_WorkBook(OpenWB);

            return true;
        }

        private bool Create_Department_Report(string department, Excel.Workbook workbook, int sheet_index)
        {
            //TODO (Kien #2#): implement Create report for one department ==> DONE
            Excel.Worksheet xlSheet;
            xlSheet = (Excel.Worksheet)OpenWB.Worksheets.get_Item(sheet_index);

            // if (Load_Department_Card_List(department))
            if (Load_Department_Account(department))
            {
                Add_Header(xlSheet, department);
                foreach (DataRow card_row in Deparment_Account_dtb.Rows)
                {
                    Add_Report_for_Each_Account(xlSheet, card_row["UserName"].ToString(), department);
                }

                Add_End_of_Sheet(xlSheet, department);
            }
            return true;
        }

        private bool Add_Header(Excel.Worksheet xlSheet, string department)
        {
            xlSheet.Cells[1, 1] = "CÔNG TY TNHH DATALOGIC SCANNING VIETNAM";
            xlSheet.Cells[2, 1] = "Address: Lô I-4B Khu Công Nghệ Cao, Quận 9, Tp.HCM";
            xlSheet.Cells[4, 5] = "TAXI FEE REPORT";
            ((Excel.Range) xlSheet.Cells[3, 5]).Font.Bold = true;
            ((Excel.Range) xlSheet.Cells[3, 5]).Font.Size = 20;
            xlSheet.Cells[5, 5] = "DEPARTMENT: " + department;
            ((Excel.Range)xlSheet.Cells[4, 5]).Font.Bold = true;
            ((Excel.Range)xlSheet.Cells[4, 5]).Font.Size = 15;

            xlSheet.Cells[7, 1] = "VinaSun";
            xlSheet.Cells[8, 1] = "From Date: ";
            xlSheet.Cells[8, 2] = VS_dateTimePicker_From.Value.Date;
            xlSheet.Cells[9, 1] = "To Date: ";
            xlSheet.Cells[9, 2] = VS_dateTimePicker_To.Value.Date;

            xlSheet.Cells[7, 5] = "MaiLinh";
            xlSheet.Cells[8, 5] = "From Date: ";
            xlSheet.Cells[8, 6] = ML_dateTimePicker_From.Value.Date;
            xlSheet.Cells[9, 5] = "To Date: ";
            xlSheet.Cells[9, 6] = ML_dateTimePicker_To.Value.Date;
            Current_Excel_Row = 11;
            xlSheet.Name = department;
            return true;
        }

        private bool Add_Report_for_Each_Account(Excel.Worksheet xlSheet, string username, string department)
        {
            DataRow[] bill_list = Bill_List_dtb.Select("UserName = '" + username + "'");
            int socot = Bill_List_dtb.Columns.Count;
            int sohang = bill_list.Count();
            int i, j;
            int total_fee = 0, total_other_fee = 0, business_fee = 0, persnal_fee = 0;

            xlSheet.Cells[Current_Excel_Row, 1] = "Report for: " + username;
            xlSheet.Cells[Current_Excel_Row + 1, 1] = "Department: " + department;
            Current_Excel_Row += 3;

            //điền tiêu đề cho các cột trong file excel
            for (i = 0; i < socot; i++)
            {
                xlSheet.Cells[Current_Excel_Row, i + 3] = Bill_List_dtb.Columns[i].ColumnName;
            }
            //dien cot stt
            xlSheet.Cells[Current_Excel_Row, 2] = "STT";
            for (i = 0; i < sohang; i++)
            {
                xlSheet.Cells[i + Current_Excel_Row + 1, 2] = i + 1;
            }
            Current_Excel_Row++;

            //dien du lieu vao sheet

            for (i = 0; i < sohang; i++)
            {
                // Cost calculate
                if (bill_list[i]["Fee"] != DBNull.Value)
                {
                    total_fee += Convert.ToInt32(bill_list[i]["Fee"]);
                }
                if (bill_list[i]["Others_Fee"] != DBNull.Value)
                {
                    total_other_fee += Convert.ToInt32(bill_list[i]["Others_Fee"]);
                }

                // Business fee & personal fee
                if (bill_list[i]["Business"] != DBNull.Value)
                {
                    if (bill_list[i]["Business"].ToString().Trim() == "Y")
                    {
                        if (bill_list[i]["Fee"] != DBNull.Value)
                        {
                            business_fee += Convert.ToInt32(bill_list[i]["Fee"]);
                        }
                        if (bill_list[i]["Others_Fee"] != DBNull.Value)
                        {
                            business_fee += Convert.ToInt32(bill_list[i]["Others_Fee"]);
                        }
                    }
                    else
                    {
                        if (bill_list[i]["Fee"] != DBNull.Value)
                        {
                            persnal_fee += Convert.ToInt32(bill_list[i]["Fee"]);
                        }
                        if (bill_list[i]["Others_Fee"] != DBNull.Value)
                        {
                            persnal_fee += Convert.ToInt32(bill_list[i]["Others_Fee"]);
                        }
                    }
                }
                else
                {
                    if (bill_list[i]["Fee"] != DBNull.Value)
                    {
                        persnal_fee += Convert.ToInt32(bill_list[i]["Fee"]);
                    }
                    if (bill_list[i]["Others_Fee"] != DBNull.Value)
                    {
                        persnal_fee += Convert.ToInt32(bill_list[i]["Others_Fee"]);
                    }
                }
                for (j = 0; j < socot; j++)
                {
                    if ((Bill_List_dtb.Columns[j].ColumnName.Trim() != "Fee")
                        && (Bill_List_dtb.Columns[j].ColumnName.Trim() != "Others_Fee")
                        && (Bill_List_dtb.Columns[j].ColumnName.Trim() != "Bill_date")
                        && (Bill_List_dtb.Columns[j].ColumnName.Trim() != "Change_DateTime"))
                    {
                        ((Excel.Range)xlSheet.Cells[i + Current_Excel_Row, j + 3]).NumberFormat = "@";
                    }
                    xlSheet.Cells[i + Current_Excel_Row, j + 3] = bill_list[i][j].ToString() == "" ? bill_list[i][j] : bill_list[i][j].ToString().Trim();
                }
            }
            Current_Excel_Row += sohang;

            xlSheet.Cells[Current_Excel_Row, 2] = "Business Cost = " + business_fee;
            xlSheet.Cells[Current_Excel_Row + 1, 2] = "Personal Cost = " + persnal_fee;
            xlSheet.Cells[Current_Excel_Row + 2, 2] = "Total Cost = " + (business_fee + persnal_fee);
            Current_Excel_Row += 5;

            DataRow new_row = Department_Cost.NewRow();
            new_row["Card_Owner"] = username;
            new_row["Personal"] = persnal_fee;
            new_row["Business"] = business_fee;
            new_row["Total"] = business_fee + persnal_fee;
            Department_Cost.Rows.Add(new_row);

            //autofit độ rộng cho các cột
            //for (i = 0; i < socot; i++)
            //{
            //    ((Excel.Range)xlSheet.Cells[1, i + 1]).EntireColumn.AutoFit();
            //}
            return true;
        }

        private bool Add_End_of_Sheet(Excel.Worksheet xlSheet, string department)
        {
            int socot = Department_Cost.Columns.Count;
            int sohang = Department_Cost.Rows.Count;
            int i;
            int business = 0, personal = 0, total_cost = 0;

            // Title
            xlSheet.get_Range(xlSheet.Cells[Current_Excel_Row, 2], xlSheet.Cells[Current_Excel_Row, 2 + socot]).Merge(false);
            xlSheet.Cells[Current_Excel_Row, 2] = "Summary";

            //căn lề cho tiêu đề
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).HorizontalAlignment = Excel.Constants.xlCenter;
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).Font.Bold = true;
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).VerticalAlignment = Excel.Constants.xlCenter;
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).Font.Size = 15;
            //màu nền cho tiêu đề
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).Interior.ColorIndex = 20;
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).RowHeight = 30;
            Current_Excel_Row++;

            //dien cot stt
            xlSheet.Cells[Current_Excel_Row, 2] = "STT";
            for (i = 0; i < sohang; i++)
            {
                xlSheet.Cells[Current_Excel_Row + i + 1, 2] = i + 1;
            }

            //điền tiêu đề cho các cột trong file excel
            for (i = 0; i < socot; i++)
            {
                xlSheet.Cells[Current_Excel_Row, i + 3] = Department_Cost.Columns[i].ColumnName;
            }
            Current_Excel_Row++;

            // Fill data
            foreach (DataRow row in  Department_Cost.Rows)
            {
                for (i = 0; i < socot; i++)
                {
                    if ((Department_Cost.Columns[i].ColumnName.Trim() != "Personal")
                        && (Department_Cost.Columns[i].ColumnName.Trim() != "Business")
                        && (Department_Cost.Columns[i].ColumnName.Trim() != "Total"))
                    {
                        ((Excel.Range)xlSheet.Cells[Current_Excel_Row, i + 3]).NumberFormat = "@";
                    }
                    xlSheet.Cells[Current_Excel_Row, i + 3] = row[i];
                }
                if (row["Personal"] != DBNull.Value)
                {
                    personal += Convert.ToInt32(row["Personal"]);
                }
                if (row["Business"] != DBNull.Value)
                {
                    business += Convert.ToInt32(row["Business"]);
                }
                if (row["Total"] != DBNull.Value)
                {
                    total_cost += Convert.ToInt32(row["Total"]);
                }
                Current_Excel_Row++;
            }
            xlSheet.Cells[Current_Excel_Row + 1, 3] = "Personal: ";
            xlSheet.Cells[Current_Excel_Row + 1, 2 + socot] = personal;

            xlSheet.Cells[Current_Excel_Row + 2, 3] = "Business: ";
            xlSheet.Cells[Current_Excel_Row + 2, 2 + socot] = business;

            xlSheet.Cells[Current_Excel_Row + 3, 3] = "Total: ";
            xlSheet.Cells[Current_Excel_Row + 3, 2 + socot] = total_cost;

            // Store Cost Of Department
            DataRow cost_row = Total_Cost.NewRow();
            cost_row["Department"] = department;
            cost_row["Personal"] = personal;
            cost_row["Business"] = business;
            cost_row["Total"] = personal + business;
            Total_Cost.Rows.Add(cost_row);

            Current_Excel_Row = 0;
            Department_Cost.Clear();
            return true;
        }

        private bool Create_Sumary_Report_All(Excel.Worksheet xlSheet)
        {
            int socot = Total_Cost.Columns.Count;
            int sohang = Total_Cost.Rows.Count;
            int i;
            int business = 0, personal = 0, total_cost = 0;


            Add_Header(xlSheet, "All Department");

            // Title
            xlSheet.get_Range(xlSheet.Cells[Current_Excel_Row, 2], xlSheet.Cells[Current_Excel_Row, 2 + socot]).Merge(false);
            xlSheet.Cells[Current_Excel_Row, 2] = "Summary";

            //căn lề cho tiêu đề
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).HorizontalAlignment = Excel.Constants.xlCenter;
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).Font.Bold = true;
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).VerticalAlignment = Excel.Constants.xlCenter;
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).Font.Size = 15;
            //màu nền cho tiêu đề
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).Interior.ColorIndex = 20;
            ((Excel.Range)xlSheet.Cells[Current_Excel_Row, 2]).RowHeight = 30;
            Current_Excel_Row++;

            //dien cot stt
            xlSheet.Cells[Current_Excel_Row, 2] = "STT";
            for (i = 0; i < sohang; i++)
            {
                xlSheet.Cells[Current_Excel_Row + i + 1, 2] = i + 1;
            }

            //điền tiêu đề cho các cột trong file excel
            for (i = 0; i < socot; i++)
            {
                xlSheet.Cells[Current_Excel_Row, i + 3] = Total_Cost.Columns[i].ColumnName;
            }
            Current_Excel_Row++;

            // Fill data
            foreach (DataRow row in Total_Cost.Rows)
            {
                for (i = 0; i < socot; i++)
                {
                    xlSheet.Cells[Current_Excel_Row, i + 3] = row[i];
                }
                if (row["Personal"] != DBNull.Value)
                {
                    personal += Convert.ToInt32(row["Personal"]);
                }
                if (row["Business"] != DBNull.Value)
                {
                    business += Convert.ToInt32(row["Business"]);
                }
                if (row["Total"] != DBNull.Value)
                {
                    total_cost += Convert.ToInt32(row["Total"]);
                }
                Current_Excel_Row++;
            }
            xlSheet.Cells[Current_Excel_Row + 1, 3] = "Personal: ";
            xlSheet.Cells[Current_Excel_Row + 1, 2 + socot] = personal;

            xlSheet.Cells[Current_Excel_Row + 2, 3] = "Business: ";
            xlSheet.Cells[Current_Excel_Row + 2, 2 + socot] = business;

            xlSheet.Cells[Current_Excel_Row + 3, 3] = "Total: ";
            xlSheet.Cells[Current_Excel_Row + 3, 2 + socot] = total_cost;

            Current_Excel_Row = 0;
            Total_Cost.Clear();
            return true;
        }

        private bool Create_EachPerson_Report_All(Excel.Worksheet card_xlSheet, Excel.Worksheet personal_xlSheet)
        {
            DataTable Card_Sumary_Report = new DataTable();
            DataTable Personal_Report = new DataTable();
            DataSet card_owner_list_dataset = new DataSet();
            SqlDataAdapter card_owner_list_dataAdapter = new SqlDataAdapter();
            DataTable card_list;
            DataRow[] bill_list;
            int budget;
            string username;
            int business_fee, persnal_fee;
            string bugget_str;

            // Create table for Card Sumary Report
            Card_Sumary_Report.Columns.Add("Taxi_Provider_Name");
            Card_Sumary_Report.Columns.Add("Card_Provider_Name");
            Card_Sumary_Report.Columns.Add("Personal");
            Card_Sumary_Report.Columns.Add("Business");
            Card_Sumary_Report.Columns.Add("Total");

            // Create Table For Personal Report
            Personal_Report.Columns.Add("UserName");
            Personal_Report.Columns.Add("Empl_ID");
            Personal_Report.Columns.Add("Group_ID");
            Personal_Report.Columns.Add("Personal");
            Personal_Report.Columns.Add("Business");
            Personal_Report.Columns.Add("Total");
            Personal_Report.Columns.Add("Budget");
            Personal_Report.Columns.Add("Over_Budget");

            foreach (DataRow card_name in Card_Table_Form.Data_dtb.Rows)
            {
                bill_list = Bill_List_dtb.Select("Card_Provider_Name = '" + card_name["Card_Provider_Name"].ToString().Trim() + "'");
                business_fee = 0;
                persnal_fee = 0;
                // Create port for each card
                foreach (DataRow bill in bill_list)
                {
                    // Business fee & personal fee
                    if (bill["Business"] != DBNull.Value)
                    {
                        if (bill["Business"].ToString().Trim() == "Y")
                        {
                            if (bill["Fee"] != DBNull.Value)
                            {
                                business_fee += Convert.ToInt32(bill["Fee"]);
                            }
                            if (bill["Others_Fee"] != DBNull.Value)
                            {
                                business_fee += Convert.ToInt32(bill["Others_Fee"]);
                            }
                        }
                        else
                        {
                            if (bill["Fee"] != DBNull.Value)
                            {
                                persnal_fee += Convert.ToInt32(bill["Fee"]);
                            }
                            if (bill["Others_Fee"] != DBNull.Value)
                            {
                                persnal_fee += Convert.ToInt32(bill["Others_Fee"]);
                            }
                        }
                    }
                    else
                    {
                        if (bill["Fee"] != DBNull.Value)
                        {
                            persnal_fee += Convert.ToInt32(bill["Fee"]);
                        }
                        if (bill["Others_Fee"] != DBNull.Value)
                        {
                            persnal_fee += Convert.ToInt32(bill["Others_Fee"]);
                        }
                    }
                }
                DataRow new_card_info = Card_Sumary_Report.NewRow();
                new_card_info["Taxi_Provider_Name"] = card_name["Taxi_Provider_Name"].ToString().Trim();
                new_card_info["Card_Provider_Name"] = card_name["Card_Provider_Name"].ToString().Trim();
                new_card_info["Personal"] = persnal_fee;
                new_card_info["Business"] = business_fee;
                new_card_info["Total"] = persnal_fee + business_fee;
                Card_Sumary_Report.Rows.Add(new_card_info);
            }

            //TODO (Kien #4#): Write Datatable to excel File ==> DONE
            Add_Header(card_xlSheet, "Card Sumary Report");
            int lines = Write_DataTable2Excel(card_xlSheet, Card_Sumary_Report, Current_Excel_Row);
            Current_Excel_Row += lines;
            Current_Excel_Row = 0;


            //2. Report for each card owner
            foreach (DataRow card_owner in Account_TBL_Form.Data_dtb.Rows)
            {
                // Report_Finance_for_CardOwner(card_owner);
                username = card_owner["UserName"].ToString().Trim();
                card_list = Load_Personal_Card_List(username);

                bill_list = Bill_List_dtb.Select("UserName = '" + username + "'");
                business_fee = 0;
                persnal_fee = 0;
                foreach (DataRow bill in bill_list)
                {
                    // Business fee & personal fee
                    if (bill["Business"] != DBNull.Value)
                    {
                        if (bill["Business"].ToString().Trim() == "Y")
                        {
                            if (bill["Fee"] != DBNull.Value)
                            {
                                business_fee += Convert.ToInt32(bill["Fee"]);
                            }
                            if (bill["Others_Fee"] != DBNull.Value)
                            {
                                business_fee += Convert.ToInt32(bill["Others_Fee"]);
                            }
                        }
                        else
                        {
                            if (bill["Fee"] != DBNull.Value)
                            {
                                persnal_fee += Convert.ToInt32(bill["Fee"]);
                            }
                            if (bill["Others_Fee"] != DBNull.Value)
                            {
                                persnal_fee += Convert.ToInt32(bill["Others_Fee"]);
                            }
                        }
                    }
                    else
                    {
                        if (bill["Fee"] != DBNull.Value)
                        {
                            persnal_fee += Convert.ToInt32(bill["Fee"]);
                        }
                        if (bill["Others_Fee"] != DBNull.Value)
                        {
                            persnal_fee += Convert.ToInt32(bill["Others_Fee"]);
                        }
                    }
                }
                DataRow personal_info = Personal_Report.NewRow();
                personal_info["UserName"] = username;
                personal_info["Empl_ID"] = card_owner["Empl_ID"].ToString().Trim();
                personal_info["Group_ID"] = card_owner["Group_ID"].ToString().Trim();
                personal_info["Personal"] = persnal_fee;
                personal_info["Business"] = business_fee;
                personal_info["Total"] = persnal_fee + business_fee;
                // bugget_str = card_owner["Budget"].ToString().Trim();
                budget = card_owner["Budget"] != DBNull.Value ? Convert.ToInt32(card_owner["Budget"]) : 0;
                personal_info["Budget"] = budget;
                if (budget < (persnal_fee + business_fee))
                {
                    personal_info["Over_Budget"] = (persnal_fee + business_fee) - budget;
                }
                else 
                {
                    personal_info["Over_Budget"] = 0;
                }
                Personal_Report.Rows.Add(personal_info);
            } // End: TODO (Kien #3#): Personal Report: Calculate Data & Add to datatable ==> DONE

            Add_Header(personal_xlSheet, "Personal Report");
            lines = Write_DataTable2Excel(personal_xlSheet, Personal_Report, Current_Excel_Row);
            Current_Excel_Row += lines;
            Current_Excel_Row = 0;

            return true;
        }

        private int Write_DataTable2Excel(Excel.Worksheet xlSheet, DataTable tabel, int start_row)
        {
            int socot = tabel.Columns.Count;
            int sohang = tabel.Rows.Count;
            int i;

            //dien cot stt
            xlSheet.Cells[Current_Excel_Row, 2] = "STT";
            for (i = 0; i < sohang; i++)
            {
                xlSheet.Cells[Current_Excel_Row + i + 1, 2] = i + 1;
            }

            //điền tiêu đề cho các cột trong file excel
            for (i = 0; i < socot; i++)
            {
                xlSheet.Cells[Current_Excel_Row, i + 3] = tabel.Columns[i].ColumnName;
            }
            Current_Excel_Row++;

            // Fill data
            foreach (DataRow row in tabel.Rows)
            {
                for (i = 0; i < socot; i++)
                {
                    if ((tabel.Columns[i].ColumnName.Trim() != "Personal")
                        && (tabel.Columns[i].ColumnName.Trim() != "Business")
                        && (tabel.Columns[i].ColumnName.Trim() != "Total")
                        && (tabel.Columns[i].ColumnName.Trim() != "Budget")
                        && (tabel.Columns[i].ColumnName.Trim() != "Over_Budget"))
                    {
                        ((Excel.Range)xlSheet.Cells[Current_Excel_Row, i + 3]).NumberFormat = "@";
                    }
                    xlSheet.Cells[Current_Excel_Row, i + 3] = row[i];
                }
                Current_Excel_Row++;
            }
            return sohang;
        }
    }
}